package sk.stuba.fiit.foo07.genex.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import sk.stuba.fiit.foo07.genex.beans.QuestionPoints;
import sk.stuba.fiit.foo07.genex.exceptions.QuestionPointsExistsException;

public class QuestionPointsDaoDerby extends BasicDao implements
        QuestionPointsDao {

    private static final String sqlAddQuestionPoints = "INSERT INTO TESTQUESTION(Points, \"Order\", testID, questionID) VALUES(?, ?, ?, ?)";
    private static final String sqlGetQuestionsPointsByTestID = "SELECT questionID, Points FROM TestQuestion WHERE testID = ? ORDER BY \"Order\" ASC";
    private static final String sqlUpdateQuestionPoints = "UPDATE TESTQUESTION SET Points = ?, \"Order\" =? WHERE testID = ? AND questionID = ?";

    PreparedStatement st = null;
    ResultSet rs = null;

    public QuestionPointsDaoDerby(Connection con) {
        super(con);
    }

    @Override
    public void addQuestionsPoints(Integer testID, ArrayList<QuestionPoints> qps)
            throws QuestionPointsExistsException, SQLException {
        try {
            int order = 0;
            st = con.prepareStatement(sqlAddQuestionPoints);
            for (QuestionPoints qp : qps) {
                st.setFloat(1, qp.getPoints());
                st.setInt(2, order);
                st.setInt(3, testID);
                st.setInt(4, qp.getQuestionId());
                st.executeUpdate();
                order++;
            }
        } catch (SQLException e) {
            if (e.getSQLState().equals("23505")) {
                throw new QuestionPointsExistsException();
            } else
                throw e;
        }

    }

    @Override
    public ArrayList<QuestionPoints> getQuestionPointsByTestID(Integer testID)
            throws SQLException {
        ArrayList<QuestionPoints> arr = null;
        try {
            st = con.prepareStatement(sqlGetQuestionsPointsByTestID,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            st.setInt(1, testID);

            rs = st.executeQuery();
            rs.last();
            arr = new ArrayList<QuestionPoints>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                arr.add(new QuestionPoints(rs.getInt("questionID"), rs
                        .getFloat("Points")));
            }
            rs.close();
            return arr;
        } catch (SQLException e) {
            st = null;
            throw e;
        }
    }

    @Override
    public void updateQuestionPoints(Integer testID,
            ArrayList<QuestionPoints> qps) throws SQLException {
        int order = 0;
        st = con.prepareStatement(sqlUpdateQuestionPoints);
        st.setInt(3, testID);
        for (QuestionPoints qp : qps) {
            st.setFloat(1, qp.getPoints());
            st.setInt(2, order++);
            st.setInt(4, qp.getQuestionId());
            st.executeUpdate();
        }
        st = null;
    }

}
